CREATE DATABASE HIS					
	ON						
		(NAME='DataFile_1'					
		,FILENAME='D:\HIS\DataFile_1.mdf')					
	LOG ON						
		(NAME='LogFile_1'					
		,FILENAME='D:\HIS\LogFile_1.ldf');		
USE HIS;					
IF OBJECT_ID('tb_Patient') IS NOT NULL					
	DROP TABLE tb_Patient;				
GO					
CREATE TABLE tb_Patient					
	(No 				
		CHAR(10) 			
		NOT NULL			
		CONSTRAINT pk_Patient_No			
			PRIMARY KEY(No)		
		CONSTRAINT ck_Patient_No			
			CHECK(No LIKE '[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]')		
	,Name 				
		VARCHAR(20) 			
		NOT NULL			
	,Gender				
		BIT			
		NOT NULL			
	,BirthDate				
		DATE			
        NOT NULL	
	,Age				
		VARCHAR(20)			
		NOT NULL			
	,Hyzk
	    VARCHAR(100)
	,Zy
	    VARCHAR(10)
	,Md
	    VARCHAR(10)
	    NOT NULL
	,Sfzh
	    VARCHAR(20)
	    NOT NULL
	,Tel
	    VARCHAR(20)
	    NOT NULL);
USE HIS;					
IF OBJECT_ID('tb_Doctor') IS NOT NULL					
	DROP TABLE tb_Doctor;				
GO					
CREATE TABLE tb_Doctort					
	(No 				
		CHAR(10) 			
		NOT NULL			
		CONSTRAINT pk_Doctor_No			
			PRIMARY KEY(No)		
		CONSTRAINT ck_Doctor_No			
			CHECK(No LIKE '[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]')		
	,Name 				
		VARCHAR(20) 			
		NOT NULL			
	,Gender				
		BIT			
		NOT NULL			
	,BirthDate				
		DATE			
        NOT NULL	
	,Age				
		VARCHAR(20)			
		NOT NULL
    ,SectionNumber
        CHAR(10)
        NOT NULL
    ,Section
        CHAR(10)
        NOT NULL);
USE HIS;					
IF OBJECT_ID('tb_HospitalDepartments') IS NOT NULL					
	DROP TABLE tb_HospitalDepartments;				
GO					
CREATE TABLE tb_HospitalDepartments					
	(No 				
		CHAR(10) 			
		NOT NULL			
		CONSTRAINT pk_HospitalDepartments_No			
			PRIMARY KEY(No)		
		CONSTRAINT ck_HospitalDepartments_No			
			CHECK(No LIKE '[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]')		
	,Name 				
		VARCHAR(20) 			
		NOT NULL);			
USE HIS;					
IF OBJECT_ID('tb_Gh') IS NOT NULL					
	DROP TABLE tb_Gh;				
GO					
CREATE TABLE tb_Gh					
	(No 				
		CHAR(8) 			
		NOT NULL			
		CONSTRAINT pk_Gh_No			
			PRIMARY KEY(No)		
		CONSTRAINT ck_Gh_No			
			CHECK(No LIKE '[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]')		
	,Name 				
		VARCHAR(20) 			
		NOT NULL			
	,Ghlb				
		VARCHAR(20)			
		NOT NULL			
	,Zzys				
		VARCHAR(20)			
        NOT NULL	
	,Ghrxm				
		VARCHAR(20)			
		NOT NULL
    ,Ghks
        CHAR(20)
        NOT NULL
    ,Gh_date
        DATE
        NOT NULL);			    
USE HIS;					
IF OBJECT_ID('tb_Bl') IS NOT NULL					
	DROP TABLE tb_Bl;				
GO					
CREATE TABLE tb_Bl					
	(No 				
		CHAR(8) 			
		NOT NULL			
		CONSTRAINT pk_Bl_No			
			PRIMARY KEY(No)		
		CONSTRAINT ck_Bl_No			
			CHECK(No LIKE '[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]')		
	,Br_Name 				
		VARCHAR(20) 			
		NOT NULL			
	,Zzys_Name				
		VARCHAR(20)			
		NOT NULL			
	,Zd_Date				
		Date		
        NOT NULL	
	,Bl_Con				
		VARCHAR(100)			
		NOT NULL);
USE HIS;					
IF OBJECT_ID('tb_Cf') IS NOT NULL					
	DROP TABLE tb_Cf;				
GO					
CREATE TABLE tb_Cf					
	(No 				
		CHAR(8) 			
		NOT NULL			
		CONSTRAINT pk_Cf_No			
			PRIMARY KEY(No)		
		CONSTRAINT ck_Cf_No			
			CHECK(No LIKE '[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]')		
	,Br_Name 				
		VARCHAR(20) 			
		NOT NULL			
	,Zzys_Name				
		VARCHAR(20)			
		NOT NULL			
	,Br_Sex
		BIT		
        NOT NULL	
    ,Br_Age
        CHAR(4)
        NOT NULL
	,Cf_Con				
		VARCHAR(100)			
		NOT NULL);   
USE HIS;					
IF OBJECT_ID('tb_Sf') IS NOT NULL					
	DROP TABLE tb_Sf;				
GO					
CREATE TABLE tb_Sf					
	(No 				
		CHAR(8) 			
		NOT NULL			
		CONSTRAINT pk_Sf_No			
			PRIMARY KEY(No)		
		CONSTRAINT ck_Sf_No			
			CHECK(No LIKE '[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]')		
	,Gh_Fee 				
		VARCHAR(10) 			
		NOT NULL			
	,Yp_Fee				
		VARCHAR(10)			
		NOT NULL			
	,Jc_Fee
		VARCHAR(10)	
        NOT NULL	
    ,Jy_Fee
        CHAR(10)
        NOT NULL);
USE HIS;					
IF OBJECT_ID('tb_Physician') IS NOT NULL					
	DROP TABLE tb_Physician;				
GO					
CREATE TABLE tb_Physician					
	(No 				
		CHAR(8) 			
		NOT NULL			
		CONSTRAINT pk_Physician_No			
			PRIMARY KEY(No)		
		CONSTRAINT ck_Physician_No			
			CHECK(No LIKE '[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]')		
	,Ks 				
		VARCHAR(10) 			
		NOT NULL			
	,Dz_Date				
		DATE		
		NOT NULL);			
USE HIS;					
IF OBJECT_ID('tb_Jc') IS NOT NULL					
	DROP TABLE tb_Jc;				
GO					
CREATE TABLE tb_Jc					
	(No 				
		CHAR(8) 			
		NOT NULL			
		CONSTRAINT pk_Jc_No			
			PRIMARY KEY(No)		
		CONSTRAINT ck_Jc_No			
			CHECK(No LIKE '[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]')		
	,Jcys 				
		VARCHAR(20) 			
		NOT NULL			
	,Jc_Date				
		DATE		
		NOT NULL
	,Jc_con
	    VARCHAR(100)
	    NOT NULL
	,Jcfx
	    VARCHAR(100)
	    NOT NULL
	,Jcig
	    VARCHAR(100)
	    NOT NULL
	,Jc_fee
	    VARCHAR(10)
	    NOT NULL);			  
USE HIS;					
IF OBJECT_ID('tb_Mz') IS NOT NULL					
	DROP TABLE tb_Mz;				
GO					
CREATE TABLE tb_Mz					
	(No 				
		CHAR(8) 			
		NOT NULL			
		CONSTRAINT pk_Mz_No			
			PRIMARY KEY(No)		
		CONSTRAINT ck_Mz_No			
			CHECK(No LIKE '[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]')		
	,Age 				
		VARCHAR(4) 			
		NOT NULL			
	,Sex				
		BIT		
		NOT NULL
	,Jc_Date
	    Date
	    NOT NULL
	,Jcqk
	    VARCHAR(100)
	    NOT NULL);
			                      
      			